﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using Agents.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Web.Security;
using System.Data;
using System.Globalization;
using System.Data.Common;
using System.Transactions;

namespace Agents
{
    //This class holds all of the Queries for accessing the database.
    //Every Method is adifferent query.
    public class Queries
    {
        //creating an instance of the model container that holds the database conection.
        Model1Container context = new Model1Container();

        #region Login

        /// <summary>
        /// Resets the user password to default
        /// </summary>
        /// <param name="username"></param>
        public string ResetPassword(string username)
        {
            return Membership.GetUser(username).ResetPassword();
        }

        /// <summary>
        /// Returns all the users
        /// </summary>
        /// <returns></returns>
        public IQueryable<Agents> GetAllUsers()
        {
            return context.Agents;
        }

        #endregion

        #region Categories

        /// <summary>
        /// Returns all the categories
        /// </summary>
        /// <returns></returns>
        public IQueryable<Categories> GetAllCategories()
        {
            return context.Categories.OrderBy((c) => c.Name);
        }

        /// <summary>
        /// Returns all the products in this category
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public IQueryable<Product> GetAllProductsOfCategory(int categoryId)
        {
            return context.Products.Where((p) => p.CategoryId == categoryId);
        }

        public void AddCategory(string Name)
        {
            //function creats new product category and adds given information to its details.

            Categories c = new Categories();
            c.Name = Name;
            context.AddToCategories(c);
            context.SaveChanges();
        }

        public bool CategoryExists(string name)
        {
            //function checks if the given product name exists in database
            return context.Categories.Any((c) => c.Name == name);
        }

        /// <summary>
        /// Get a category by its id
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public Categories GetCategory(int categoryId)
        {
            return context.Categories.FirstOrDefault((c) => c.Id == categoryId);
        }

        #endregion

        #region Clients

        /// <summary>
        /// Make sure there is no name colision
        /// </summary>
        /// <param name="p"></param>
        /// <param name="p_2"></param>
        /// <returns></returns>
        public bool CheckClientNameColision(int clientId, string name)
        {
            return !(context.Clients.Any((c) => (c.Id != clientId) && 
                (c.Name.Equals(name))));
        }

        /// <summary>
        /// Adds a client to the DB
        /// </summary>
        /// <param name="name"></param>
        /// <param name="city"></param>
        /// <param name="street"></param>
        /// <param name="streetNum"></param>
        /// <param name="telephone"></param>
        /// <param name="email"></param>
        /// <param name="priority"></param>
        /// <param name="regionId"></param>
        /// <param name="creditCardLevel"></param>
        /// <param name="contact"></param>
        /// <param name="cp"></param>
        /// <param name="salesTypeId"></param>
        /// <param name="status"></param>
        /// <param name="xC"></param>
        /// <param name="yC"></param>
        public void AddClient(string name, string city, string street, string streetNum, 
            string telephone, string email, string priority, int regionId,
            string creditCardLevel, string contact, string cp, int salesTypeId, 
            string status, double xC, double yC)
        {
            Clients client = new Clients();
            client.Name = name;
            client.City = city;
            client.Street = street;
            client.StreetNum = streetNum;
            client.Phone = telephone;
            client.EMail = email;
            client.Priority = priority;
            client.Region = regionId;
            client.CreditCardLevel = creditCardLevel;
            client.ContactPerson = contact;
            client.CP = cp;
            client.SaleType = salesTypeId;
            client.Status = status;
            client.AvgOrder = 0;
            client.LastOrder = null;
            client.x = xC;
            client.y = yC;
            context.AddToClients(client);
            context.SaveChanges();
        }

        public bool ClientExists(string name)
        {
            //function checks if the given client name exists in database
            return context.Clients.Any((c) => c.Name == name);
        }

        /// <summary>
        /// Edit client details in the DB
        /// </summary>
        /// <param name="name"></param>
        /// <param name="city"></param>
        /// <param name="street"></param>
        /// <param name="streetNum"></param>
        /// <param name="telephone"></param>
        /// <param name="email"></param>
        /// <param name="priority"></param>
        /// <param name="regionId"></param>
        /// <param name="creditCardLevel"></param>
        /// <param name="contact"></param>
        /// <param name="cp"></param>
        /// <param name="salesTypeId"></param>
        /// <param name="status"></param>
        /// <param name="xC"></param>
        /// <param name="yC"></para>
        public void EditClient(int clientId, string name, string city, string street, string streetNum,
            string telephone, string email, string priority, int regionId,
            string creditCardLevel, string contact, string cp, int salesTypeId,
            string status, double xC, double yC)
        {
            Clients client = GetClient(clientId);

            client.Name = name;
            client.City = city;
            client.Street = street;
            client.StreetNum = streetNum;
            client.Phone = telephone;
            client.EMail = email;
            client.Priority = priority;
            client.Region = regionId;
            client.CreditCardLevel = creditCardLevel;
            client.ContactPerson = contact;
            client.CP = cp;
            client.SaleType = salesTypeId;
            client.Status = status;
            context.SaveChanges();
        }

        public void UpdateCriterea(double dist, double total, double last, double avg, double pri)
        {
            //function changes the percenteges for each of the five criterea for the  main algorithm
            Configuration config = context.Configuration.FirstOrDefault();
            config.Distance = dist;
            config.TotalOrders = total;
            config.LastOrder = last;
            config.AvgMoney = avg;
            config.Priority = pri;
            context.SaveChanges();
        }

        public Clients GetClient(string name)
        {
            //function returns the client with the given name.
            return context.Clients.FirstOrDefault((c) => c.Name == name);
        }

        /// <summary>
        /// Returns client by his Id
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public Clients GetClient(int Id)
        {
            return context.Clients.FirstOrDefault((c) => c.Id == Id);
        }

        public void ClientToInactive(int clientId)
        {
            Clients client = context.Clients.FirstOrDefault((c) => c.Id == clientId);
            if (client != null)
            {
                client.Status = "לא פעיל";
            }
            context.SaveChanges();
        }

        public void ChooseClient(string username, string c_name)
        {
            //function changes agnts status to "going to client"
            //function changes clients status to Active.
            Agents agent = GetAgentByUserName(username);
            Clients client = GetClient(c_name);

            agent.Status = c_name + " בדרך ל";
            client.Status = "Active";
            context.SaveChanges();
        }

        #endregion

        #region Agents

        /// <summary>
        /// Get agent by Id
        /// </summary>
        /// <param name="agentId"></param>
        /// <returns></returns>
        public Agents GetAgent(int agentId)
        {
            return context.Agents.FirstOrDefault((a) => a.Id == agentId);
        }

        /// <summary>
        /// Adds a new agent to the DB
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="tel"></param>
        /// <param name="id"></param>
        /// <param name="mail"></param>
        /// <param name="loc"></param>
        /// <param name="user"></param>
        /// <param name="pass"></param>
        public void AddAgent(string name, string tel, string id, string mail,
                            int regionId, string user, string pass)
        {
            // Start a transaction
            using (TransactionScope transaction = new TransactionScope())
            {
                // Add the agent to the Agents table
                Agents agent = new Agents()
                {
                    Name = name,
                    Phone = tel,
                    EMail = mail,
                    Region = regionId,
                    UserName = user,
                    TZ = id,
                    WavgTime = 0,
                    Mhours = 0,
                    Whours = 0,
                    Wincome = 0,
                    Mincome = 0,
                    Morders = 0,
                    Worders = 0,
                    Status = "לא במשמרת",
                    Type = "Agent",
                    StartShift = null,
                    EndShift = null
                };

                context.AddToAgents(agent);
                context.SaveChanges();

                // Add a new user to the SQL Membership provider
                Membership.CreateUser(user, pass);
                Roles.AddUserToRole(user, "Agent");

                // Commit the transaction
                transaction.Complete();
            }
        }

        public bool AgentExists(string name)
        {
            //function checks if the given agent name exists in database
            return context.Agents.Any((a) => a.Name == name);
        }

        /// <summary>
        /// Make sure there is no name colision
        /// </summary>
        /// <returns></returns>
        public bool CheckAgentNameColision(int agentId, string name)
        {
            return !(context.Agents.Any((a) => (a.Id != agentId) &&
                (a.Name.Equals(name))));
        }

        /// <summary>
        /// Edit the agent's details
        /// </summary>
        /// <param name="agentId"></param>
        /// <param name="Name"></param>
        /// <param name="tel"></param>
        /// <param name="TZ"></param>
        /// <param name="mail"></param>
        /// <param name="loc"></param>
        /// <param name="username"></param>
        public void EditAgent(int agentId, string Name, string tel, string TZ, string mail, int regionId)
        {
            Agents agent = context.Agents.FirstOrDefault((a) => a.Id == agentId);

            agent.Name = Name;
            agent.TZ = TZ;
            agent.Phone = tel;
            agent.EMail = mail;
            agent.Region = regionId;

            context.SaveChanges();
        }

        /// <summary>
        /// Deletes the agent with this Id
        /// </summary>
        /// <param name="agentId"></param>
        public void DeleteAgent(int agentId)
        {
            context.Connection.Open();

            // Start a transaction
            using (TransactionScope transaction = new TransactionScope())
            {
                Agents agent = context.Agents.FirstOrDefault((a) => a.Id == agentId);

                // Delete the agent's user
                Membership.DeleteUser(agent.UserName);

                // Delete the agent from the Agents table
                context.DeleteObject(agent);
                context.SaveChanges();

                // Commit the transaction
                transaction.Complete();
            }
        }

        //function returns the agent with the given name
        public Agents GetAgent(string name)
        {
            return context.Agents.FirstOrDefault((a) => a.Name == name);
        }

        /// <summary>
        /// Changes the given agents status to "on shift"
        /// </summary>
        /// <param name="agent"></param>
        public void ChangeToActive(Agents agent)
        {
            // Get the agent again because this object may be disconnected (Linq bug...)
            Agents currAgent = context.Agents.FirstOrDefault((a) => a.Id == agent.Id);
            currAgent.Status = "במשמרת";
            context.SaveChanges();
        }

        /// <summary>
        /// End the agent's shift
        /// </summary>
        /// <param name="agent"></param>
        public void EndShift(Agents agent)
        {
            agent.Status = "לא במשמרת";
            agent.EndShift = DateTime.Now;

            // Add working hours
            double time;
            TimeSpan ts = new TimeSpan();
            DateTime start, end;

            start = (DateTime)agent.StartShift;
            end = (DateTime)agent.EndShift;
            ts = end.Subtract(start);
            time = ts.TotalHours;
            agent.Mhours = agent.Mhours + time;
            agent.Whours = agent.Whours + time;

            // Add the end shift to the agent's action log
            AgentsActionLog action = new AgentsActionLog();
            action.AgentId = agent.Id;
            action.Date = DateTime.Now;
            action.Description = "סיים משמרת";
            context.AddToAgentsActionLog(action);
            context.SaveChanges();
        }

        /// <summary>
        /// Updates the agent status and writes to the agent's log
        /// </summary>
        /// <param name="agent"></param>
        /// <param name="client"></param>
        public void ChangeGoingTo(Agents agent, Clients client)
        {
            agent.Status = client.Name;

            AgentsActionLog action = new AgentsActionLog();
            action.AgentId = agent.Id;
            action.Date = DateTime.Now;
            action.Description = "מתחיל נסיעה ל: " + client.Name;
            context.AddToAgentsActionLog(action);
            context.SaveChanges();
        }

        /// <summary>
        /// Add to the agent's log that he left the client
        /// </summary>
        /// <param name="agent"></param>
        /// <param name="client"></param>
        /// <param name="reason"></param>
        public void AgentLeftClient(Agents agent, string reason)
        {
            AgentsActionLog action = new AgentsActionLog();
            action.AgentId = agent.Id;
            action.Date = DateTime.Now;
            action.Description = reason;
            context.AddToAgentsActionLog(action);
            context.SaveChanges();
        }

        /// <summary>
        /// Returns the agent by his user name
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public Agents GetAgentByUserName(string userName)
        {
            return context.Agents.FirstOrDefault((a) => a.UserName == userName);
        }


        /// <summary>
        /// Start the agent's shift
        /// </summary>
        /// <param name="agent"></param>
        public void StartShift(Agents agent)
        {
            agent.Status = "במשמרת";
            agent.StartShift = DateTime.Now;

            // Add the start shift to the agent's action log
            AgentsActionLog action = new AgentsActionLog();
            action.AgentId = agent.Id;
            action.Date = DateTime.Now;
            action.Description = "התחיל משמרת";
            context.AddToAgentsActionLog(action);
            context.SaveChanges();
        }

        #endregion

        #region Client Order

        /// <summary>
        /// Create a new order
        /// </summary>
        /// <param name="client">The name of the client</param>
        /// <param name="sum">The sum of the current order</param>
        /// <param name="agent">The name of the agent</param>
        /// <param name="notes">The notes for the order</param>
        public void MakeOrder(string client, double sum, string agent, string notes, 
                              Dictionary<int, int> products)
        {
            // Start a transaction
            using (TransactionScope transaction = new TransactionScope())
            {
                // Update information of the client that made the order
                Clients currClient = 
                    context.Clients.FirstOrDefault((c) => c.Name == client);
                double avg = currClient.AvgOrder;
                double total = currClient.TotalOrders + 1;
                currClient.TotalOrders++;
                currClient.AvgOrder = (avg + sum) / total;
                currClient.LastOrder = DateTime.Now;
                context.SaveChanges();

                // Update information of the agent that took the order
                Agents currAgent = 
                    context.Agents.FirstOrDefault((c) => c.Name == agent);
                currAgent.Morders++;
                currAgent.Worders++;
                currAgent.Mincome += sum;
                currAgent.Wincome += sum;
                context.SaveChanges();

                // Save the order to the orders table
                Orders currOrder = new Orders();
                currOrder.AgentId = currAgent.Id;
                currOrder.ClientId = currClient.Id;
                currOrder.Date = DateTime.Now;
                currOrder.Sum = sum;
                currOrder.Remarks = notes;
                context.AddToOrders(currOrder);
                context.SaveChanges();

                // Update the items in stock for each product
                // and check whether to create lack notifications
                foreach (int productId in products.Keys)
                {
                    Product product = context.Products.FirstOrDefault(
                                                    (p) => p.Id == productId);
                    product.ItemsInStock -= products[productId];
                    context.SaveChanges();

                    // Items in stock is below thershold, no previous notifications, 
                    // and there is not enough items from this product in pending 
                    // stock orders.
                    if ((product.NotifyBelowThreshold) &&
                        (product.Threshold > product.ItemsInStock +
                        GetProductNumberOfItemsInPendingStockOrders(productId)) &&
                        (!context.StockLackNotifications.Any(
                                                (p) => p.ProductId == product.Id)))
                    {
                        Notifications notification = new Notifications();
                        notification.Date = DateTime.Now;
                        notification.NotificationType = (int)
                                        NotificationTypes.LACK_NOTIFICATION;
                        notification.ProductId = product.Id;
                        context.AddToNotifications(notification);
                        context.SaveChanges();
                    }

                    // Update product's last order date
                    product.LastOrderDate = DateTime.Now;

                    // Add the product to the order
                    OrdersProducts orderProduct = new OrdersProducts();
                    orderProduct.OrderId = currOrder.Id;
                    orderProduct.ProductId = product.Id;
                    orderProduct.ItemsNum = products[productId];
                    context.AddToOrdersProducts(orderProduct);
                    context.SaveChanges();

                    // If the item had dead stock notification, delete it
                    if (context.DeadStockNotifications.Any(
                                            (p) => p.ProductId == product.Id))
                    {
                        int notificationId = context.DeadStockNotifications.
                            FirstOrDefault((p) => p.ProductId == product.Id).
                            NotificationId;

                        context.DeleteObject(context.Notifications.FirstOrDefault(
                            (n) => n.Id == notificationId));
                    }

                    // Update the product's number of ordered items
                    ++product.NumOfOrders;
                    product.NumOfItemsInAllOrders += products[productId];
                }

                // Add this order to the agent log
                AgentsActionLog action = new AgentsActionLog();
                action.AgentId = currAgent.Id;
                action.Date = DateTime.Now;
                action.Description = String.Format(
                    "בצע הזמנה אצל: {0}, בסך: {1:0.00}", 
                    currClient.Name, sum);
                context.AddToAgentsActionLog(action);
                context.SaveChanges();

                // TODO: Send the order to the order center?

                // Commit the transaction
                transaction.Complete();
            }
        }

        /// <summary>
        /// Get the email of the orders center
        /// </summary>
        public string GetEmail()
        {
            return context.Configuration.FirstOrDefault().Email;
        }

        public void UpdateEmail(string mail)
        {
            context.Configuration.FirstOrDefault().Email = mail;
            context.SaveChanges();
        }

        #endregion

        #region Products

        /// <summary>
        /// Returns all the products
        /// </summary>
        /// <returns></returns>
        public IQueryable<Product> GetAllProducts()
        {
            return context.Products.OrderBy((p) => p.Name);
        }

        /// <summary>
        /// Adds a product to the DB
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="price"></param>
        /// <param name="categoryId"></param>
        /// <param name="itemsInStock"></param>
        /// <param name="minimumThreshold"></param>
        /// <param name="notifyBelowThreshold"></param>
        /// <param name="maxDaysFromLastOrder"></param>
        /// <param name="notifyDeadStock"></param>
        public void AddProduct(string name, double price, int categoryId, int itemsInStock,
            int minimumThreshold, bool notifyBelowThreshold, int maxDaysFromLastOrder,
            bool notifyDeadStock)
        {
            // Start a transaction
            using (TransactionScope transaction = new TransactionScope())
            {
                Product product = new Product()
                {
                    Name = name,
                    Price = price,
                    CategoryId = categoryId,
                    ItemsInStock = itemsInStock,
                    Threshold = minimumThreshold,
                    NotifyBelowThreshold = notifyBelowThreshold,
                    MaxDaysFromLastOrder = maxDaysFromLastOrder,
                    NotifyUnordered = notifyDeadStock
                };

                context.AddToProducts(product);
                context.SaveChanges();

                // If the product doesn't have enough in stock, 
                // create a lack notification
                if ((product.ItemsInStock < product.Threshold) &&
                    (product.NotifyBelowThreshold))
                {
                    Notifications notification = new Notifications();
                    notification.Date = DateTime.Now;
                    notification.NotificationType = (int)
                                    NotificationTypes.LACK_NOTIFICATION;
                    notification.ProductId = product.Id;
                    context.AddToNotifications(notification);
                    context.SaveChanges();
                }

                transaction.Complete();
            }
        }

        /// <summary>
        /// Make sure there is no name colision
        /// </summary>
        /// <param name="productId"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool CheckProductNameColision(int productId, string name)
        {
            return !(context.Products.Any((p) => (p.Id != productId) &&
                (p.Name.Equals(name))));
        }

        public bool ProductExists(string name)
        {
            //function checks if the given product name exists in database
            return context.Products.Any((p) => p.Name == name);
        }

        /// <summary>T
        /// Returns the number of items ordered from this product in stock orders 
        /// that are still pending.
        /// </summary>
        /// <param name="productId">The product ID</param>
        /// <returns>The number of items ordered from this product in stock orders 
        /// that are still pending.</returns>
        public int GetProductNumberOfItemsInPendingStockOrders(int productId)
        {
            int result = 0;

            if (context.StockOrdersProducts.Any((p) => p.ProductId == productId))
            {
                result = context.StockOrdersProducts.Where((p) => p.ProductId == productId).
                    Sum((p) => p.ItemsNum);
            }

            return result;
        }

        /// <summary>
        /// Edits the product details in the DB
        /// </summary>
        /// <param name="price"></param>
        /// <param name="categoryId"></param>
        /// <param name="oldName"></param>
        /// <param name="minimumThreshold"></param>
        /// <param name="notifyBelowThreshold"></param>
        /// <param name="maxDaysFromLastOrder"></param>
        /// <param name="notifyDeadStock"></param>
        public void EditProduct(int productId, string name, double price, int categoryId, 
            int minimumThreshold, bool notifyBelowThreshold, int maxDaysFromLastOrder,
            bool notifyDeadStock)
        {
            Product product = GetProduct(productId);

            // Start a transaction
            using (TransactionScope transaction = new TransactionScope())
            {
                product.Name = name;
                product.Price = price;
                product.CategoryId = categoryId;

                // Delete previous lack notifications in the following cases:
                // 1. The product had notifications before and now not.
                // 2. The product was below threshold but after the threshold changed, it is above it.
                if ((product.NotifyBelowThreshold && !notifyBelowThreshold) ||
                    (product.ItemsInStock +
                    GetProductNumberOfItemsInPendingStockOrders(product.Id) >= minimumThreshold))
                {
                    Notifications notification = product.Notifications.FirstOrDefault((n) =>
                        n.NotificationType == (int)NotificationTypes.LACK_NOTIFICATION);

                    if (notification != null)
                    {
                        context.DeleteObject(notification);
                    }
                }

                // Create new lack notifications in the following cases:
                // 1. The product didn't have notifications before.
                // 2. The product was above threshold but after the threshold changed, it is below it.
                else if ((notifyBelowThreshold) &&
                    (product.ItemsInStock +
                    GetProductNumberOfItemsInPendingStockOrders(product.Id) < minimumThreshold) &&
                    (!product.NotifyBelowThreshold ||
                    (product.ItemsInStock +
                    GetProductNumberOfItemsInPendingStockOrders(product.Id) >= product.Threshold)))
                {
                    Notifications notification = new Notifications();
                    notification.Date = DateTime.Now;
                    notification.NotificationType = (int)NotificationTypes.LACK_NOTIFICATION;
                    notification.ProductId = product.Id;
                    context.AddToNotifications(notification);
                }

                product.Threshold = minimumThreshold;
                product.NotifyBelowThreshold = notifyBelowThreshold;

                // Create new dead stock notifications if the product didn't have notifications before.
                TimeSpan daysFromLastOrder = DateTime.Now -
                    product.LastOrderDate.GetValueOrDefault(DateTime.MinValue);

                if ((notifyDeadStock) &&
                    (daysFromLastOrder.TotalDays > maxDaysFromLastOrder) &&
                    (!product.NotifyUnordered))
                {
                    Notifications notification = new Notifications();
                    notification.Date = DateTime.Now;
                    notification.NotificationType = (int)NotificationTypes.DEAD_STOCK_NOTIFICATION;
                    notification.ProductId = product.Id;
                    context.AddToNotifications(notification);
                }

                product.MaxDaysFromLastOrder = maxDaysFromLastOrder;
                product.NotifyUnordered = notifyDeadStock;

                context.SaveChanges();

                // Commit the transaction
                transaction.Complete();
            }
        }

        public void DeleteProduct(string name)
        {
            //function deletes existing product
            Product product = GetProduct(name);
            context.DeleteObject(product);
            context.SaveChanges();
        }

        public Product GetProduct(string name)
        {
            //function returns the product with the given name
            return context.Products.FirstOrDefault((p) => p.Name == name);
        }

        /// <summary>
        /// Get the product by its id
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public Product GetProduct(int productId)
        {
            return context.Products.FirstOrDefault((p) => p.Id == productId);
        }

        #endregion

        #region Price Lists

        public double GetDiscount(int clientId)
        {
            Clients client = GetClient(clientId);
            return client.PriceListSet.Discount;
        }

        public void AddPriceList(string Number, double discount)
        {
            //function creats new discount number and adds given information to its details.

            PriceList pl = new PriceList();
            pl.Number = Number;
            pl.Discount = discount;
            context.AddToPriceListSet(pl);
            context.SaveChanges();
        }

        /// <summary>
        /// Return all the price lists
        /// </summary>
        /// <returns></returns>
        public IQueryable<PriceList> GetAllPriceLists()
        {
            return context.PriceListSet;
        }

        public Boolean ExistsPriceList(string number)
        {
            //function checks if the given discount number already exists in the database
            return context.PriceListSet.Any((p) => p.Number == number);
        }

        #endregion

        #region Stock Orders

        /// <summary>
        /// Adds a new stock order to the DB
        /// </summary>
        /// <param name="orderDate">The date the order was established</param>
        /// <param name="itemsFromProduct">A list of products id's and 
        /// the number of items ordered from each product</param>
        /// <param name="sum">The price of the order</param>
        /// <returns>The new stock order ID</returns>
        public int AddNewStockOrder(DateTime orderDate, 
            Dictionary<int, int> itemsFromProduct, double sum)
        {
            // Open the order
            StockOrders stockOrder = new StockOrders();

            // Start a transaction
            using (TransactionScope transaction = new TransactionScope())
            {
                stockOrder.Date = orderDate;
                stockOrder.Sum = sum;
                stockOrder.IsPending = true;
                context.AddToStockOrders(stockOrder);
                context.SaveChanges();

                // Create products orders
                StockOrdersProducts productToOrder;
                foreach (int productId in itemsFromProduct.Keys)
                {
                    productToOrder = StockOrdersProducts.CreateStockOrdersProducts(
                        stockOrder.Id, productId, itemsFromProduct[productId]);
                    context.AddToStockOrdersProducts(productToOrder);
                    context.SaveChanges();

                    // Delete lack notifications if the number of items from the 
                    // product after order is not lower than its threshold
                    Product product = GetProduct(productId);
                    if ((product.Notifications.Any((n) =>
                        n.NotificationType == (int)NotificationTypes.LACK_NOTIFICATION)) &&
                        (product.Threshold <= product.ItemsInStock + 
                        itemsFromProduct[productId]))
                    {
                        context.DeleteObject(product.Notifications.First(
                            (n) => n.NotificationType == 
                                (int)NotificationTypes.LACK_NOTIFICATION));
                        context.SaveChanges();
                    }
                }

                // Commit the transaction
                transaction.Complete();
            }

            return stockOrder.Id;
        }

        /// <summary>
        /// Cancels the stock order
        /// </summary>
        /// <param name="stockOrderId">The ID of the stock order to cancel</param>
        public void CancelStockOrder(int stockOrderId)
        {
            // Start a transaction
            using (TransactionScope transaction = new TransactionScope())
            {
                // Delete all the products related to the stock order
                IList<StockOrdersProducts> products =
                    context.StockOrdersProducts.Where(
                    s => s.StockOrderId == stockOrderId).ToList();

                foreach (StockOrdersProducts orderProduct in products)
                {
                    context.DeleteObject(orderProduct);
                    context.SaveChanges();
                }

                // Get the stock order by its Id
                StockOrders stockOrder =
                    context.StockOrders.FirstOrDefault((StockOrders s) =>
                        s.Id == stockOrderId);

                // Delete it
                context.DeleteObject(stockOrder);
                context.SaveChanges();

                // Commit the transaction
                transaction.Complete();
            }
        }

        /// <summary>
        /// Updates the number of items from each products and deletes the stock order
        /// </summary>
        /// <param name="stockOrderId">The ID of the stock order to close</param>
        public void StockOrderArrived(int stockOrderId)
        {
            // Start a transaction
            using (TransactionScope transaction = new TransactionScope())
            {
                // Get all the products in the stock order
                IList<StockOrdersProducts> products =
                    context.StockOrdersProducts.Where(
                    s => s.StockOrderId == stockOrderId).ToList();

                foreach (StockOrdersProducts orderProduct in products)
                {
                    // Get the product
                    Product product = context.Products.FirstOrDefault(
                        (Product p) => p.Id == orderProduct.ProductId);

                    // Update the number of items in the product
                    product.ItemsInStock += orderProduct.ItemsNum;

                    // Remove the stock order products
                    context.DeleteObject(orderProduct);
                    context.SaveChanges();
                }

                // Mark the stock order as not pending
                StockOrders stockOrder =
                    context.StockOrders.FirstOrDefault(
                    (o) => o.Id == stockOrderId);
                stockOrder.IsPending = false;
                context.SaveChanges();

                // Commit the transaction
                transaction.Complete();
            }
        }

        #endregion

        #region Notifications

        /// <summary>
        /// Deletes the notification with this Id
        /// </summary>
        /// <param name="notificationId"></param>
        public void DeleteNotification(int notificationId)
        {
            Notifications notification =
                context.Notifications.FirstOrDefault((n) => n.Id == notificationId);

            if (notification != null)
            {
                context.DeleteObject(notification);
                context.SaveChanges();
            }
        }

        #endregion

        #region Regions

        /// <summary>
        /// Returns all the region
        /// </summary>
        /// <returns></returns>
        public object GetAllRegions()
        {
            return context.Regions;
        }

        #endregion

        #region Reports

        /// <summary>
        /// Get Top Five Selling Products by number of orders or by number 
        /// of items ordered in all orders
        /// </summary>
        /// <param name="creteria">Number of orders or number of items</param>
        /// <returns></returns>
        public List<Product> GetTopFiveSellingProducts(TopProductsCreteria creteria)
        {
            SqlParameter parameter = new SqlParameter("creteria", (int)creteria);
            ObjectResult<Product> result = context.ExecuteStoreQuery<Product>(
                "exec GetTopFiveProducts @creteria", parameter);
            return result.ToList<Product>();
        }

        /// <summary>
        /// Get Five Least Selling Products by number of orders or by number 
        /// of items ordered in all orders
        /// </summary>
        /// <param name="creteria">Number of orders or number of items</param>
        /// <returns></returns>
        public List<Product> GetFiveLeastSellingProducts(TopProductsCreteria creteria)
        {
            SqlParameter parameter = new SqlParameter("creteria", (int)creteria);
            ObjectResult<Product> result = context.ExecuteStoreQuery<Product>(
                "exec GetFiveLeastSellingProducts @creteria", parameter);
            return result.ToList<Product>();
        }

        /// <summary>
        /// Get the number of items ordered from this product
        /// in each month this year
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public Dictionary<String, int> GetProductSellsLastYear(int productId)
        {
            Dictionary<String, int> sellsPerMonth =
                new Dictionary<String, int>();

            // Populate the months (a year ago from today)
            CultureInfo culture = new CultureInfo("he-IL");

            // Go over all the months
            for (int month = DateTime.Now.Month, index = 0;
                index < 12; month = (month + 1) % 12, ++index)
            {
                // Determine which year
                int year = (month + 1 > DateTime.Now.Month) ? 
                    DateTime.Now.Year - 1 : DateTime.Now.Year;

                // Select all the orders in this month
                IQueryable<Orders> ordersInMonth =
                    context.Orders.Where((o) => (o.Date.Month == month + 1) &&
                        (o.Date.Year == year));

                // Find the orders of this product in this month
                IQueryable<OrdersProducts>  products =
                    context.OrdersProducts.Where((o) => (o.ProductId == productId) &&
                        (ordersInMonth.Select((order) => order.Id)).Contains(o.OrderId));

                // Sum up the number of items ordered in all orders
                int sum = products.Count() > 0 ? products.Sum((p) => p.ItemsNum) : 0;
                
                // For each month, add the month name as key and the items as value
                sellsPerMonth.Add(culture.DateTimeFormat.MonthNames[month] + " " +
                    year, sum);
            }

            return sellsPerMonth;
        }

        /// <summary>
        /// Get the number of items ordered from this product
        /// in each day this month
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public Dictionary<String, int> GetProductSellsLastMonth(int productId)
        {
            Dictionary<String, int> sellsPerDay =
                new Dictionary<String, int>();

            // Go over all the days
            for (int day = 1; day <= DateTime.Now.Day; ++day)
            {
                DateTime currDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, day);

                // Select all the orders in this day
                IQueryable<Orders> ordersInDay =
                    context.Orders.Where((o) => 
                        EntityFunctions.TruncateTime(o.Date) == currDate);

                // Find the orders of this product in this day
                IQueryable<OrdersProducts> products =
                    context.OrdersProducts.Where((o) => (o.ProductId == productId) &&
                        (ordersInDay.Select((order) => order.Id)).Contains(o.OrderId));

                // Sum up the number of items ordered in all orders
                int sum = products.Count() > 0 ? products.Sum((p) => p.ItemsNum) : 0;

                // For each day, add the date as key and the items as value
                sellsPerDay.Add(currDate.ToShortDateString(), sum);
            }

            return sellsPerDay;
        }

        /// <summary>
        /// Get balance in these dates
        /// </summary>
        /// <returns></returns>
        public Dictionary<String, Double> GetBalance(DateTime startDate, DateTime endDate)
        {
            // Prepare the parameters for the stored procedure
            SqlParameter parStartDate = new SqlParameter("startDate", startDate);
            SqlParameter parEndDate = new SqlParameter("endDate", endDate);
            SqlParameter parBalance = new SqlParameter
                ("balance", SqlDbType.Float) { Direction = ParameterDirection.Output };
            SqlParameter parExpenses = new SqlParameter
                ("expenses", SqlDbType.Float) { Direction = ParameterDirection.Output };
            SqlParameter parProfit = new SqlParameter
                ("profit", SqlDbType.Float) { Direction = ParameterDirection.Output };

            // Call the stored procedure
            context.ExecuteStoreCommand(
                "exec GetBalance @startDate, @endDate, " + 
                "@balance OUTPUT, @expenses OUTPUT, @profit OUTPUT",
                parStartDate, parEndDate, parBalance, parExpenses, parProfit);

            // Return out parameters
            return new Dictionary<String, Double>() 
            {
                {"הוצאות", Convert.ToDouble(parExpenses.Value)}, 
                {"הכנסות", Convert.ToDouble(parProfit.Value)}
            };
        }

        #endregion

        #region Location Based Notifications

        /// <summary>
        /// Return a notification by its id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public LocationBasedNotifications GetLocationBasedNotification(int id)
        {
            return context.LocationBasedNotifications.FirstOrDefault((n) => n.Id == id);
        }

        /// <summary>
        /// Delete a notification by its id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public void DeleteLocationBasedNotification(int id)
        {
            LocationBasedNotifications notification = 
                                GetLocationBasedNotification(id);
            context.DeleteObject(notification);
            context.SaveChanges();
        }

        /// <summary>
        /// Returns all the location based notifications
        /// </summary>
        /// <returns></returns>
        public IQueryable<LocationBasedNotifications> GetLocationBasedNotifications()
        {
            return context.LocationBasedNotifications;
        }

        /// <summary>
        /// Returns the location based notifications in the radius around this location
        /// </summary>
        /// <param name="x">The X Coordinate</param>
        /// <param name="y">The Y Coordinate</param>
        /// <returns></returns>
        public List<LocationBasedNotifications>
            GetLocationBasedNotifications(double x, double y)
        {
            List<LocationBasedNotifications> notifications = 
                new List<LocationBasedNotifications>();

            // Get the radius (in meters)
            int radius = context.Configuration.FirstOrDefault().
                                    LocationBasedNotificationsRadius;

            // Search for notifications in locations within this radius
            foreach (LocationBasedNotifications notification in
                    context.LocationBasedNotifications)
            {
                // Calculate the distance (sqrt(dx^2 + dy^2))
                double distance = Extensions.CalcDistance(x, y, 
                    notification.LocationX, notification.LocationY) * 1000;

                // Add this notification only if distance is smaller than the radius
                if (distance <= radius)
                {
                    notifications.Add(notification);
                }
            }

            return notifications;
        }

        /// <summary>
        /// Adds a new location based notification to the DB
        /// </summary>
        /// <param name="content">The string content of the notification</param>
        /// <param name="x">The X coordinate of the location</param>
        /// <param name="y">The Y coordinate of the location</param>
        /// <returns>The id of the created notifications</returns>
        public int AddLocationBasedNotification(String content, 
            double x, double y, String address)
        {
            LocationBasedNotifications notification = new LocationBasedNotifications()
            {
                Content = content,
                LocationX = x,
                LocationY = y,
                Address = address
            };

            context.AddToLocationBasedNotifications(notification);
            context.SaveChanges();
            return notification.Id;
        }

        /// <summary>
        /// Allow or disallow location based notifications
        /// </summary>
        /// <param name="isAllowed"></param>
        public void SetIsLocationBasedNotificationsAllowed(bool isAllowed)
        {
            Configuration config = context.Configuration.FirstOrDefault();
            config.AllowLocationBasedNotifications = isAllowed;
            context.SaveChanges();
        }

        /// <summary>
        /// Set location based notifications radius in meters
        /// </summary>
        /// <param name="radius"></param>
        public void SetLocationBasedNotificationsRadius(int radius)
        {
            Configuration config = context.Configuration.FirstOrDefault();
            config.LocationBasedNotificationsRadius = radius;
            context.SaveChanges();
        }

        #endregion

        #region Configuration

        /// <summary>
        /// Returns configuration for the Agents system
        /// </summary>
        /// <returns></returns>
        public Configuration GetConfiguration()
        {
            return context.Configuration.FirstOrDefault();
        }

        #endregion
    }
}